 # -*- coding: utf-8 -*-
"""
Created on Mon Sep 11 09:51:03 2023

@author: tyshixi08
"""
import pandas as pd 
from datetime import date  
import numpy as np
import matplotlib.pyplot as plt
import math     
from sqlalchemy import create_engine
import datetime
from collections import Counter
import Dfactor_get_origin_data as get_origin_data
from tqdm import tqdm

# 首次提取，get_data()参数为0，后面不需要再输入参数
# print("选择数据提取：\n 1.首次提取；\n 2.更新提取。\n 请输入提取类型：")
# r = int(input()) - 1
def data_filter():
    r = 1  #更新提取，首次则设置为0
    df_valuation_stock_last,df_s, date_n,last_day,date_all = get_origin_data.get_data(r)
    
    date_all = df_valuation_stock_last["Date"].unique()
    date_all = np.sort(date_all)
    last_day = df_valuation_stock_last["Date"].unique()
    print(list(last_day)[-1])
    date_n = len(last_day)
    res_n = np.zeros(shape = (date_n - 120, 15))
    res_s = np.zeros(shape = (date_n - 120, 15))
    
    print('过滤底层券')
    # 新建df_after用于存储过滤后的数据
    df_after1 = pd.DataFrame()
    #df_after1 = df_after1[df_after1["c_bondCode"] == "113578.SH"]
    #df_after1 = df_after1[(df_after1["Date"] > pd.to_datetime("2022-8-1")) & (df_after1["Date"] < pd.to_datetime("2022-8-17"))]
    for k in tqdm(range(date_n - 120)):
    
        temp_data = df_valuation_stock_last[(df_valuation_stock_last['Date'] == last_day[k+120])]
        dt = temp_data.n_outstandingBalance
        # 剔除转债规模小于2亿
        cond1 = (dt < 2)
    
        # 剔除A+及以下
        dt = temp_data.c_issuerRating2
        cond3 = ((dt != "AAA") & (dt != "AA+") & (dt != "AA") & (dt != "AA-"))
        #cond3 = ((dt != "AAA") & (dt != "AA+") & (dt != "AA") & (dt != "AA-")  & (dt != "A+"))
    
        start_20 = date_all[np.where(date_all == last_day[k+120])[0][0] - 20]
        dt_20 = df_valuation_stock_last[(df_valuation_stock_last['Date'] > start_20) & (df_valuation_stock_last['Date'] <= last_day[k+120])]
        dt_20 = dt_20[["Date", "c_bondCode", "n_amt", "n_outstandingBalance"]]
        dt_20_res = dt_20.groupby("c_bondCode").mean()
        
        #日均成交额低于100万
        c = (dt_20_res['n_amt'] < 1000000)
        c = c[c]
        cond4 = (temp_data["c_bondCode"].isin(c.index))
    
        #补充计算债券换手率，换手率大于200%
        '''
        转债上市时间：不足1周剔除/剩余半年以内（强制回售期）；
        转债停牌；
        转债面临强赎（考虑是出强赎公告即剔除，是否有指标值）；
    
        '''
        #上市不足30
        temp_data = temp_data.copy()
        temp_data.t_ipoDate = pd.to_datetime(temp_data.t_ipoDate)
        residual_Day = temp_data.Date-temp_data.t_ipoDate
        cond6 = (residual_Day <= datetime.timedelta(days=30))
    
    
        # 正股停牌
        dt = temp_data.t_warning
        cond8 = (dt ==1)
        
    
        bond_id = temp_data.c_bondCode.unique()
        start_120 = date_all[np.where(date_all == last_day[k+120])[0][0] - 120]
        dt_120 = df_s[(df_s['TRADE_DT'] > start_120) & (df_s['TRADE_DT'] <= last_day[k+120])]
        dt_120 = dt_120[["TRADE_DT", "S_INFO_WINDCODE", "S_DQ_VOLUME"]]
        dt_120_res = dt_120.groupby("S_INFO_WINDCODE").S_DQ_VOLUME.sum()
        #A 股股票的上市公司，连续 120 个交易日通过本所交易系统实现的累计股票成交量低于 500万股
        c = (dt_120_res < 50000)
        c = c[c]
        cond_120 = (temp_data["c_code"].isin(c.index))
        
        start_20 = date_all[np.where(date_all == last_day[k+120])[0][0] - 20]
        dt_20 = df_valuation_stock_last[(df_valuation_stock_last['Date'] > start_20) & (df_valuation_stock_last['Date'] <= last_day[k+120])]
        dt_20 = dt_20[["Date", "c_bondCode", "S_DQ_CLOSE", "S_VAL_MV"]]
        dt_20_res = dt_20.groupby("c_bondCode").S_DQ_CLOSE.mean()
        #连续 20 个交易日的每日股票收盘价均低于 1 元；
        c = (dt_20_res < 1)
        c = c[c]
        cond_20_close = (temp_data["c_bondCode"].isin(c.index))
        #上市公司连续 20 个交易日在本所的每日股票收盘总市值均低于 3 亿元；
        c = (dt_20_res < 3)
        c = c[c]
        cond_20_mv = (temp_data["c_bondCode"].isin(c.index))
        #最近一个会计年度经审计的净利润为负值且营业收入低于 1 亿元，或追溯重述后最近一个会计年度净利润为负值且营业收入低于 1 亿元；
        cond_jlr_yysr = (temp_data["NET_PROFIT_EXCL_MIN_INT_INC"] < 0) &(temp_data["OPER_REV"] < 1E9)
        #最近一个会计年度经审计的期末净资产为负值，或追溯重述后最近一个会计年度期末净资产为负值；
        cond_jzc = (temp_data.TOT_SHRHLDR_EQY_EXCL_MIN_INT < 0)
        # 最近一个会计年度的财务会计报告被出具无法表示意见或否定意见的审计报告；
        cond_sjyj = (temp_data.S_STMNOTE_AUDIT_CATEGORY== 405005000) | (temp_data.S_STMNOTE_AUDIT_CATEGORY== 405004000)
        
    
        bond_id = temp_data.c_bondCode.unique()
        start_30 = date_all[np.where(date_all == last_day[k+120])[0][0] - 30]
        dt_30 = df_valuation_stock_last[(df_valuation_stock_last['Date'] > start_30) & (df_valuation_stock_last['Date'] <= last_day[k+120])]
        
    
        dt_30 = dt_30[["Date", "c_bondCode", "n_convValue"]]
        dt_30 = dt_30[dt_30['n_convValue'] >= 130]
        dt_30_res = dt_30.groupby("c_bondCode").n_convValue.count()
        cond_qs = (dt_30_res >= 14)
        code = list(cond_qs[cond_qs == True].index)
        cond_qzsh = (temp_data["c_bondCode"].isin(code))
        n = len(temp_data.c_bondCode.unique())
    
        
        conv_value = temp_data.n_outstandingBalance
        res_n[k] = np.array([np.sum(cond1), np.sum(cond3), np.sum(cond4), np.sum(cond6), np.sum(cond8),\
                             np.sum(cond_120),np.sum(cond_20_close), np.sum(cond_20_mv), np.sum(cond_jlr_yysr), np.sum(cond_jzc), np.sum(cond_sjyj),\
                             np.sum(cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj ),np.sum(cond_qzsh),\
                             np.sum(cond1 | cond3 | cond4 | cond6 | cond8 | cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj ), n*n])/n
            
        new_data = temp_data[(cond1 | cond3 | cond4 | cond6 | cond8 | cond_120 | cond_20_close | cond_20_mv | cond_jlr_yysr | cond_jzc | cond_sjyj ) == False]
        df_after1 = pd.concat([df_after1, new_data])
        
        
    condition = ["剔除转债规模小于2亿", "剔除A+及以下", "剔除日均成交额低于100万", "剔除上市不足30日", "剔除正股状态",\
                 "连续 120 个交易日通过本所交易系统实现的累计股票成交量低于 500万股", "连续 20 个交易日的每日股票收盘价均低于 1 元", \
                 "上市公司连续 20 个交易日在本所的每日股票收盘总市值均低于 3 亿元", "最近一个会计年度经审计的净利润为负值且营业收入低于 1 亿元，或追溯重述后最近一个会计年度净利润为负值且营业收入低于 1 亿元；", \
                 "最近一个会计年度经审计的期末净资产为负值，或追溯重述后最近一个会计年度期末净资产为负值；", \
                 "最近一个会计年度的财务会计报告被出具无法表示意见或否定意见的审计报告","退市风险全部条件","剔除强制赎回","以上全部条件均剔除", "转债总数"]
    output_condition_n = pd.DataFrame(data= res_n,columns=condition)
    output_condition_n["Date"] = last_day[120:]
    
    # 0925新增n_bondPremiumRatio, n_implied_vol, 1008新增n_bondValue, 1010新增n_ytmCB, 1011新增S_DQ_ADJFACTOR
    ind = ["Date","c_bondCode","n_convValue",'n_convPrice',"n_convPremiumRatio","n_amt","n_outstandingBalance","n_high","n_low",\
           "n_close","n_open","n_pctChange",'ret_nextday_close','ret_nextday_open',"n_volume","n_bondPremiumRatio","n_implied_vol",'n_bondValue','n_ytmCB',\
           "S_DQ_HIGH", "S_DQ_LOW","S_DQ_CLOSE","S_DQ_PCTCHANGE","S_VAL_MV","S_DFA_NETPROFIT_TTM","S_DFA_DEDUCTEDPROFIT_TTM","S_DQ_ADJFACTOR"]
    
    def return_cal(df_slice):
        # 计算收益率
        df_slice = df_slice.copy()
        df_slice = df_slice[df_slice.n_close != 0]
        df_slice['Date'] = pd.to_datetime(df_slice['Date'])
        df_slice = df_slice.sort_values(by='Date', ascending=True)
        df_slice['ret_close'] = df_slice['n_close'] / df_slice['n_close'].shift(1) - 1
        df_slice['ret_nextday_close'] = df_slice['ret_close'].shift(-2)
        df_slice['ret_open'] = df_slice['n_open'] / df_slice['n_open'].shift(1) - 1
        df_slice['ret_nextday_open'] = df_slice['ret_open'].shift(-2)
        return df_slice    
    
    df_valuation_stock_last = df_valuation_stock_last.groupby('c_bondCode').apply(return_cal).reset_index(drop=True)
    df_valuation_stock_last = df_valuation_stock_last[ind]
    df_valuation_stock_last.to_csv(r"data/data_all.csv", index = False)
        
    df_after1 = df_after1.merge(df_valuation_stock_last[['c_bondCode','Date','ret_nextday_close','ret_nextday_open']], on=['c_bondCode','Date'], how='left')
    df_after1 = df_after1[ind]
    df_after1.to_csv(r"data/data_after_filter.csv", index = False)
    #df_after1.to_csv(r"data_after_filter2.csv", index = False) # 放开信评条件
    return

if __name__ == '__main__':
    data_filter()


